package com.whjk.system.data.uitls;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.whjk.system.data.annotation.ExcelDropdown;
import com.whjk.system.data.vo.InquiryTemplateVo;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;


import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;


@Slf4j
public class ExcelImportUtils<T extends Object> {

    private Class<T> clazz;

    // 对应列的下拉列表
    Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();

    //用户设置表头
    List<List<String>> head = new ArrayList<>();

    //用户设置后的内容顺序
    Map<Integer, String> modifiedMap = new TreeMap<>();

    //对应日期位置列表
    List<Integer> dateList = new ArrayList<>();

    public ExcelImportUtils(Class<T> clazz) {
        this.clazz = clazz;
    }

    //导入
    public List<T> excelImport(InputStream stream) {

        List<T> list = new ArrayList<>();

        EasyExcel.read(stream, InquiryTemplateVo.class, new AnalysisEventListener<T>() {
            @SneakyThrows
            @Override
            public void invoke(T o, AnalysisContext analysisContext) {
                list.add(o);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                log.debug("导入完成！");
            }
        }).sheet().doRead();

        return list;
    }


    /**
     * 下载
     *
     * @param res       响应
     * @param data      下载的数据
     * @param fileName  文件名
     * @param sheetName 表名
     * @throws Exception
     */
    public void downLoad(HttpServletResponse res, List<T> data, String fileName, String sheetName) throws Exception {
        populateDictExpValues(data);
        setMapDropDown(this.clazz);
        //解决下载文件名重复问题,如果文件名不存在，不修改文件名；如果存在在文件名。在文件名后面加数字
        String timestamp = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String modifiedFileName = fileName + "_" + timestamp;

        if (head.size() > 0) {
            EasyExcelFactory.write(getOutputStream(modifiedFileName, res), this.clazz).sheet(sheetName)
                    .registerConverter(new LocalDateTimeConverter())////LocalDateTime 类型导出无法转换
                    .registerWriteHandler(new DropdownWriteHandler())//自定义处理器：单元格下拉列表格式,列的自适应宽度,文本类型
                    .head(head)
                    .doWrite(initData(data));
        } else {
            EasyExcelFactory.write(getOutputStream(modifiedFileName, res), this.clazz).sheet(sheetName)
                    .registerConverter(new LocalDateTimeConverter())
                    .registerWriteHandler(new DropdownWriteHandler())
                    .doWrite(data);
        }

    }


    //响应头封装
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    //LocalDateTime 类型导出无法转换,导出列的自适应宽度，及文本样式
    class LocalDateTimeConverter implements Converter<LocalDateTime> {

        @Override
        public Class<LocalDateTime> supportJavaTypeKey() {
            return LocalDateTime.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }

        @Override
        public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
            return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        }

        @Override
        public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                                   GlobalConfiguration globalConfiguration) {
            return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        }

    }

    //自定义处理器：单元格下拉列表格式
    class DropdownWriteHandler extends AbstractColumnWidthStyleStrategy implements SheetWriteHandler {
        private static final int MAX_COLUMN_WIDTH = 255;
        private final Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(8);

        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

            int columnCount = writeSheetHolder.getSheet().getDefaultColumnWidth();
            SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();
            CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
            // 设置为文本格式
            cellStyle.setDataFormat((short) 49);
            for (int i = 0; i < columnCount; i++) {
                // i为列，一整列设置为文本格式和水平居中
                sxssfSheet.setDefaultColumnStyle(i, cellStyle);
            }


            ///开始设置下拉框
            Sheet sheet = writeSheetHolder.getSheet();

            DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
            for (Map.Entry<Integer, Map<String, Object>> entry : mapDropDown.entrySet()) {
                /***起始行、终止行、起始列、终止列**/
                CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, entry.getKey(), entry.getKey());  // 检查的区域
                /***设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint((String[]) entry.getValue().get("val"));
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                /***处理Excel兼容性问题**/
                if (dataValidation instanceof XSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(true);  // 验证输入数据是否真确
                    dataValidation.setShowErrorBox(!(boolean) entry.getValue().get("isAllow"));  // 输入无效值时是否显示错误框
                    dataValidation.setShowPromptBox(!(boolean) entry.getValue().get("isAllow"));  // 设置无效值时 是否弹出提示框
                    dataValidation.createPromptBox("温馨提示", "只能选择列表中的值!!!");   // 设置无效值时的提示框内容
                    sheet.addValidationData(dataValidation);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(dataValidation);
            }
        }

        @Override
        public void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
                                   Integer relativeRowIndex, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), key -> new HashMap<>(16));

                Integer columnWidth = dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0 && columnWidth <= MAX_COLUMN_WIDTH) {
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }
                }
            }
        }

        private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                CellData cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                }

                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

    //下拉列表
    private void setMapDropDown(Class clazz) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        if (fields == null) {
            throw new Exception("属性为空");
        }
        for (Field field : fields) {
            // DateTimeFormat注解
            if (field.isAnnotationPresent(DateTimeFormat.class)) {
                int index = field.getAnnotation(ExcelProperty.class).index(); // 位置
                dateList.add(index);
            }

            // 判断这个字段上是否有相应的注解信息（ExcelDropdown.class）
            if (field.isAnnotationPresent(ExcelDropdown.class) && field.isAnnotationPresent(ExcelProperty.class)) {
                int index = field.getAnnotation(ExcelProperty.class).index(); // 位置
                String[] values = field.getAnnotation(ExcelDropdown.class).value(); // 下拉列表的value内容
                if (values.length == 0) { // 如果value为空数组，则跳过处理
                    continue;
                }

                boolean allowOtherValue = field.getAnnotation(ExcelDropdown.class).isAllowOtherValue(); // 下拉列表的isAllowOtherValue值

                Map<String, Object> map = new HashMap<>();
                map.put("val", values);
                map.put("isAllow", allowOtherValue);
                mapDropDown.put(index, map);
            }
        }
    }

    //设置对应属性的@ExcelDropdown注解的对应属性的对应值。
    // 如：fieldNames[0] 对应 attrNames[0] 对应 attrValues[0]， 否则出错。
    public void setExcelDropdownValue(String fieldNames, List<String> attrValues) throws Exception {
        AnnotationUtil<T> s = new AnnotationUtil<T>(this.clazz);
        //ExcelDropdown注解只有value属性
        //更新字段上ExcelDropdown注解的value属性的值
        s.updateAnnoAttrValue(fieldNames, ExcelDropdown.class, "value", attrValues);
    }

    //数据字典替换数据
    public void populateDictExpValues(List<T> dataList) {
        for (Object data : dataList) {
            populateDictExpValues(data);
        }
    }

    private static void populateDictExpValues(Object data) {
        Class<?> clazz = data.getClass();

        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            ExcelDropdown fieldAnnotation = field.getAnnotation(ExcelDropdown.class);
            if (fieldAnnotation == null) {
                continue;
            }

            try {
                Object value = field.get(data);
                if (value instanceof String) {
                    String dictExp = fieldAnnotation.dictExp();
                    Map<String, String> mapping = parseDictExp(dictExp);
                    if (mapping.containsKey(value)) {
                        String replacement = mapping.get(value);
                        field.set(data, replacement);
                    }
                }
            } catch (IllegalAccessException e) {
                // 异常处理逻辑
            }
        }
    }

    private static Map<String, String> parseDictExp(String dictExp) {
        LinkedHashMap<String, String> mapping = new LinkedHashMap<>();
        String[] pairs = dictExp.split(",");
        for (String pair : pairs) {
            String[] keyValue = pair.split("=");
            if (keyValue.length == 2) {
                mapping.put(keyValue[0], keyValue[1]);
            }
        }
        return mapping;
    }


    //设置表头数据
/*    public void SelectedHeaders(List<String> headList) {
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                String fieldName = field.getName();

                if (headList.contains(fieldName)) {
                    List<String> selectedHeaders = new ArrayList<>();
                    selectedHeaders.add(Arrays.toString(annotation.value()));
                    head.add(selectedHeaders);

                }
            }
        }
    }//需要根据ExcelProperty注解里index从小到大顺序，排列headList的顺序，并添加到selectedHeaders*/

    //根据modifiedMap的value值去修改对应的字段名上ExcelProperty注解中的index值，index值修改为modifiedMap的key
    public void SelectedHeaders(List<String> headList) {
        Field[] fields = clazz.getDeclaredFields();
        Map<Integer, String> selectedMap = new TreeMap<>(); // 使用TreeMap按键排序的特性

        for (Field field : fields) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                int index = annotation.index();
                String fieldName = field.getName();

                if (headList.contains(fieldName)) {
                    selectedMap.put(index, fieldName);
                }
            }
        }

        // 创建一个新的映射，用于存储修改后的键值对
        int index = 0;
        // 遍历已排序的selectedMap
        for (Map.Entry<Integer, String> entry : selectedMap.entrySet()) {
            // 获取原始键值对
            int originalKey = entry.getKey();
            String value = entry.getValue();
            // 将原始值替换为递增的键值
            modifiedMap.put(index, value);
            index++;

            for (Field field : fields) {
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                if (annotation != null) {
                    String fieldName = field.getName();

                    if (fieldName.equals(value)) {
                        List<String> selectedHeaders = new ArrayList<>();
                        String toString = Arrays.toString(annotation.value());
                        selectedHeaders.add(toString.substring(1,toString.length()-1));
                        head.add(selectedHeaders);
                    }
                }
            }

        }
    }


    //设置表头数据顺序
    public List<List<Object>> initData(List<T> dataList) throws NoSuchFieldException, IllegalAccessException {
        List<List<Object>> result = new ArrayList<>();
        for (T data : dataList) {
            List<Object> row = new ArrayList<>();
            for (Map.Entry<Integer, String> entry : modifiedMap.entrySet()) {
                int index = entry.getKey();
                String fieldName = entry.getValue();
                Field field = data.getClass().getDeclaredField(fieldName);
                field.setAccessible(true);
                row.add(field.get(data));
            }
            result.add(row);
        }
        return result;
    }


}

